// 拆分字符串，根据工程名称单元格获取具体工程名称
export function getProjectName(str) {
  const regex = /(?<=工程名称：).*/
  return str.match(regex)[0]
}

// 将如A1-B5这样的范围性叙述，扩展成期间各单元格{x,y}对象的数组，如[{x:0，y:1},{x:0，y:2}...]
export function excelCellsToXY(range) {
  let rangeArr = range.split('-')
  let startMatrix = excelCellToXY(rangeArr[0])
  let endMatrix = excelCellToXY(rangeArr[1])
  let result = []
  if (startMatrix.x > endMatrix.x) [startMatrix.x, endMatrix.x] = [endMatrix.x, startMatrix.x]
  if (startMatrix.y > endMatrix.y) [startMatrix.y, endMatrix.y] = [endMatrix.y, startMatrix.y]
  for (let i = startMatrix.x; i <= endMatrix.x; i++)
    for (let j = startMatrix.y; j <= endMatrix.y; j++) result.push({ x: i, y: j })

  return result
}

// 获取一块如A1-B5这样范围内单元格的内容，并返回内容对象数组
export function getExcelCellContent(worksheet, range) {
  const tableTitle = []
  getLocationsKeys(range).forEach((cell) => {
    const value =
      worksheet.getCell(cell).value !== null
        ? worksheet
            .getCell(cell)
            .value.toString()
            .replace(/ /g, '')
            .replace(/\n/g, '')
            .replace(/\r/g, '')
            .replace(/\t/g, '')
        : ''
    const address = worksheet.getCell(cell).address
    tableTitle.push({ address, value })
  })
  return tableTitle
}

// 将如A1-B5这样的范围性叙述，扩展成各个单元格坐标的数组,如['A1','A2'...]
export function getLocationsKeys(range) {
  let rangeArr = range.split('-')
  let startString = rangeArr[0]
  let endString = rangeArr[1]

  var reg = /[A-Z]{1,}/g
  let startCol = startString.match(reg)[0]
  let startRow = Number(startString.substring(startString.search(/[0-9]+$/))) // 提取行号

  let endCol = endString.match(reg)[0]
  let endRow = Number(endString.substring(endString.search(/[0-9]+$/))) // 提取行号

  if (startCol > endCol) [startCol, endCol] = [endCol, startCol]
  if (startRow > endRow) [startRow, endRow] = [endRow, startRow]

  let totalStart = 0 // 共有多少个
  for (let index = 0; index < startCol.length; index++) {
    totalStart += Math.pow(26, startCol.length - index - 1) * (startCol.charCodeAt(index) - 64)
  }

  let totalEnd = 0 // 共有多少个
  for (let index = 0; index < endCol.length; index++) {
    totalEnd += Math.pow(26, endCol.length - index - 1) * (endCol.charCodeAt(index) - 64)
  }

  let result = []
  for (let i = totalStart; i < totalEnd + 1; i++)
    for (let j = startRow; j <= endRow; j++) {
      result.push(getCharFromIndex(i) + j)
    }
  return result
}

// 私有函数，将数字转化为字母字符串，如1->A,27->AA
function getCharFromIndex(num) {
  let result = ''
  while (num > 0) {
    let remainder = (num - 1) % 26
    result = String.fromCharCode(65 + remainder) + result
    num = Math.floor((num - 1) / 26)
  }
  return result
}

// 将单个单元格转成XY坐标对象
export function excelCellToXY(cell) {
  let x = 0
  let col = cell.substring(0, cell.search(/[0-9]+$/)).toUpperCase() // 提取列标
  let row = Number(cell.substring(cell.search(/[0-9]+$/))) // 提取行号

  for (let i = 0; i < col.length; i++) {
    x += (col.charCodeAt(i) - 'A'.charCodeAt(0) + 1) * Math.pow(26, col.length - i - 1)
  }

  return { x: x - 1, y: row - 1 } // 坐标需要减去1，因为Excel中的坐标是从1开始的
}

// 返回传入列区间内数组
export function getColsArr(worksheet, colsStr) {
  const worksheetRowCount = worksheet.rowCount
  const colArr = colsStrToArr(colsStr)
  let result = []
  colArr.forEach((col) => {
    for (let index = 1; index <= worksheetRowCount; index++)
      result.push({
        address: col + index,
        value:
          worksheet.getCell(col + index).value !== null
            ? worksheet
                .getCell(col + index)
                .value.toString()
                .replace(/ /g, '')
            : ''
      })
  })
  return result
}

// 将列区间转换成列数组
function colsStrToArr(colsStr) {
  const colsRange = globalReplace(colsStr, '列', '')
  let colsRangeArr = colsRange.split('-')
  let startCol = colsRangeArr[0].toUpperCase()
  let endCol = colsRangeArr[1].toUpperCase()
  if (startCol > endCol) [startCol, endCol] = [endCol, startCol]
  let totalStart = 0 // 共有多少个
  for (let index = 0; index < startCol.length; index++) {
    totalStart += Math.pow(26, startCol.length - index - 1) * (startCol.charCodeAt(index) - 64)
  }

  let totalEnd = 0 // 共有多少个
  for (let index = 0; index < endCol.length; index++) {
    totalEnd += Math.pow(26, endCol.length - index - 1) * (endCol.charCodeAt(index) - 64)
  }
  let colsArr = []
  for (let index = totalStart; index <= totalEnd; index++) {
    colsArr.push(getCharFromIndex(index))
  }
  return colsArr
}

// 字符串全局替换
function globalReplace(string, search, replacement) {
  // 创建一个全局正则表达式对象
  const regex = new RegExp(search, 'g')
  // 执行替换操作
  return string.replace(regex, replacement)
}

// 判断数组是否具有重复元素
export function hasDuplicateElements(arr) {
  return new Set(arr).size !== arr.length
}

// 获取数组重复元素
export function getDuplicates(arr) {
  let duplicates = []
  let set = new Set()
  for (let i = 0; i < arr.length; i++) {
    if (set.has(arr[i]) && !duplicates.includes(arr[i])) {
      duplicates.push(arr[i])
    } else {
      set.add(arr[i])
    }
  }
  return duplicates
}

// 判断数组内元素是否在给定数组内，任一元素存在即返回true
export function hasElementInArray(arr, compareArr) {
  return arr.some((item) => compareArr.includes(item))
}
// 判断数组内元素是否等于给定对象数组内对象的value值，任一元素存在即返回一个包含true和对象的address的对象
export function hasElementInArrayWithValue(arr, compareArr) {
  let address
  let exist = arr.some((item) =>
    compareArr.some((compareItem) => {
      if (compareItem.value === item) {
        address = compareItem.address
        return true
      }
      return false
    })
  )
  return {
    address,
    exist
  }
}

// 判断给定对象数组内对象的value值是否包含给定字符串，返回一个包含address和exist属性的对象
export function includeStr(compareArr, str) {
  let address
  let exist = compareArr.some((item) => {
    if (item.value.includes(str)) {
      address = item.address
      return true
    }
    return false
  })
  return {
    address,
    exist
  }
}

// 判断给定对象数组内对象的value值是否以给定字符串结尾，返回一个包含address和exist属性的对象
export function endWithStr(compareArr, str) {
  let address
  let exist = compareArr.some((item) => {
    if (item.value.toString().endsWith(str)) {
      address = item.address
      return true
    }
    return false
  })
  return {
    address,
    exist
  }
}
